﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using InformationSystem.Entity;

namespace InformationSystem.Dao
{
	/// <summary>
	/// Description of ProgrammeDao.
	/// </summary>
	public class ProgrammeDao : BaseDao, IDao<Programme>
	{
		public ProgrammeDao()
		{
		}

		#region IDao implementation

		public bool add(Programme programme)
		{
            bool result = true;

            helper.beginTrans();

			String sql = "insert into t_programme ( name, status, level, addDate, year) values"
                                             + "(@name,@status,@level,datetime('now', 'localtime'), @year)";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",programme.name.ToString()),
				helper.createSqliteParam("@status",programme.status.ToString()),
				helper.createSqliteParam("@level",programme.level.ToString()),
                helper.createSqliteParam("@year",programme.year.ToString())
			};

            if (helper.ExcWithTrans(sql, param) == 1)
            {
                //获取最新插入的id
                SQLiteDataReader reader = helper.Read("select last_insert_rowid()");

                int lastId = 0;

                while (reader.Read())
                {
                    lastId = reader.GetInt32(0);
                }
                reader.Close();
                reader.Dispose();

                if (lastId == 0)
                {
                    result = false;

                }
                else
                {
                    for (int i = 0; i < programme.moduleIds.Count; i++)
                    {
                        int id = programme.moduleIds[i];

                        sql = "insert into t_programme_module (programmeId,moduleId, addDate) values "
                                            + "(@programmeId,@moduleId,datetime('now', 'localtime'))";

                        SQLiteParameter[] p = 
			        {   
                        helper.createSqliteParam("@programmeId",lastId.ToString()),
				        helper.createSqliteParam("@moduleId",id.ToString())
                        
			        };

                        if (helper.ExcWithTrans(sql, p) != 1)
                        {
                            result = false;
                        }
                    }
                }
                

            }
            else
            {
                result = false;
            }

            if (result)
            {
                helper.commit();
            }
            else
            {
                helper.rollback();
            }

            return result;
		}

		public bool del(Programme programme)
		{
			String sql = "delete from t_programme where id = @id";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@id",programme.id.ToString())
			};

            return helper.Exc(sql, param) == 1;
		}

		public bool update(Programme programme)
		{
            bool result = true;

            helper.beginTrans();

			String sql = "update t_programme  set name=@name, status=@status, level=@level, year=@year where id = @id";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",programme.name.ToString()),
				helper.createSqliteParam("@status",programme.status.ToString()),
				helper.createSqliteParam("@level",programme.level.ToString()),
                helper.createSqliteParam("@year",programme.year.ToString()),
                helper.createSqliteParam("@id",programme.id.ToString())
			};

            if (helper.ExcWithTrans(sql, param) == 1)
            {
                //1. delete old record
                sql = "delete from t_programme_module where programmeId='" + programme .id+ "'";
                helper.ExcWithTrans(sql);

                //2. insert new record
                for (int i = 0; i < programme.moduleIds.Count; i++)
                {
                    int id = programme.moduleIds[i];

                    sql = "insert into t_programme_module (programmeId,moduleId, addDate) values "
                                        + "(@programmeId,@moduleId,datetime('now', 'localtime'))";

                    SQLiteParameter[] p = 
			        {   
                        helper.createSqliteParam("@programmeId",programme.id.ToString()),
				        helper.createSqliteParam("@moduleId",id.ToString())
			        };

                    if (helper.ExcWithTrans(sql, p) != 1)
                    {
                        result = false;
                    }
                }

            }
            else
            {
                result = false;

            }

            if (result)
            {
                helper.commit();
            }
            else
            {
                helper.rollback();
            }

            return result;

		}

		public Programme query(Programme programme)
		{
            String sql = "select id, name, status, level, addDate,year from t_programme where id = @id";
			SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@id",programme.id.ToString())
			};
			
			SQLiteDataReader sdr = helper.Read(sql,param);
			
			Programme retProgramme = new Programme();
			
			while (sdr.Read()) 
			{
				retProgramme.id = sdr.GetInt32(0);
				retProgramme.name = sdr.GetString(1);
				retProgramme.status = sdr.GetInt32(2);
				retProgramme.level = sdr.GetInt32(3);
				retProgramme.addDate = sdr.GetString(4);
                retProgramme.year = sdr.GetString(5);
				
			}
			
			sdr.Close();
			sdr.Dispose();
			
			
			return retProgramme;
		}

		public List<Programme> queryAll()
		{
			List<Programme> programmes = new List<Programme>();

            String sql = "select id, name, status, level, addDate,year from t_programme ";
			
			SQLiteDataReader sdr = helper.Read(sql);
			
			while (sdr.Read()) 
			{
				Programme retProgramme = new Programme();
				retProgramme.id = sdr.GetInt32(0);
				retProgramme.name = sdr.GetString(1);
				retProgramme.status = sdr.GetInt32(2);
				retProgramme.level = sdr.GetInt32(3);
				retProgramme.addDate = sdr.GetString(4);
                retProgramme.year = sdr.GetString(5);
				programmes.Add(retProgramme);
				
			}
			
			sdr.Close();
			sdr.Dispose();
			
			
			return programmes;
		}

		#endregion
		
		
		public SQLiteDataAdapter getDisplayColumn(String sql)
		{        	
        	SQLiteDataAdapter da = helper.QueryDA(sql);
        	
        	return da;
		}


        public Programme queryByName(string name)
        {
            String sql = "select id, name, status, level, addDate, year from t_programme where name = @name";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",name)
			};

            SQLiteDataReader sdr = helper.Read(sql, param);

            Programme retProgramme = new Programme();

            while (sdr.Read())
            {
                retProgramme.id = sdr.GetInt32(0);
                retProgramme.name = sdr.GetString(1);
                retProgramme.status = sdr.GetInt32(2);
                retProgramme.level = sdr.GetInt32(3);
                retProgramme.addDate = sdr.GetString(4);
                retProgramme.year = sdr.GetString(5);
            }

            sdr.Close();
            sdr.Dispose();


            return retProgramme;
        }
    }
}
